﻿/* 
 * Since 2008 - 2012
 */

package com.hh.bss.project.dao.impl;

import java.util.*;

import com.hh.bss.base.*;
import com.hh.bss.common.MapObjectRowMapper;
import com.hh.bss.common.page.*;
import com.hh.bss.common.page.impl.*;
import com.hh.bss.project.dao.*;
import com.hh.bss.project.entity.*;
import com.hh.bss.project.service.*;
import com.hh.bss.project.vo.query.*;
import com.hh.bss.util.*;





import static com.hh.bss.util.ObjectUtils.*;

import org.springframework.stereotype.Repository;

@Repository
public class SearchProjectCompanyDaoImpl extends BaseHibernateDao<SearchProjectCompany,java.lang.Integer>  implements SearchProjectCompanyDao<SearchProjectCompany,java.lang.Integer>{

	public Class getEntityClass() {
		return SearchProjectCompany.class;
	}
	public List findProjectCustomer(SearchProjectCompanyQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select distinct a0.id,a0.name,a0.full_name fullName from company a0,customer a2 where a0.id=a2.company_id ");
		sql.append(" ) pc ");
		sql.append(" where 1=1 ");
//		if(isNotEmpty(query.getProjectId())) { 	
//		  sql.append(" and pc.projectId=?  ");		
//		  values.add(query.getProjectId());
//		}
		
		
		
		return super.getJdbcDao().findByQuery(sql.toString(),values.toArray(), new MapObjectRowMapper());
		//return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	public List findList(SearchProjectCompanyQuery query) {
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		sql.append(" select * from ( ");
		sql.append(" select distinct a0.id,a0.name,a0.full_name fullName,a2.project_id projectId from company a0,search_project_company a2 where a0.id=a2.company_id ");
		sql.append(" ) pc ");
		sql.append(" where 1=1 ");		
		sql.append(" and pc.projectId=? ");
		values.add(query.getProjectId());
		return super.getJdbcDao().findByQuery(sql.toString(),values.toArray(), new MapObjectRowMapper());
		
	}
	public Page findProjectCompanyPage(SearchProjectCompanyQuery query) {
		
		
		StringBuffer sql = new StringBuffer();
		List values = new ArrayList();
		
		/*
		sql.append(" select * from ( ");
		sql.append(" select a.spcId id,a.id companyId,a.project_id projectId,a.name,a.full_name fullName,a.abbr_name abbrName, a.industry,a.property,a.type,a.tel,a.website,a.location,a.address,a.postcode,ifnull(t1.cvCount,0) cvCount,ifnull(t2.cvlvCount,0) cvlvCount,ifnull(t3.cvContactCount,0) cvContactCount,ifnull(t4.cvRecomCount,0) cvRecomCount,ifnull(t5.interviewCount,0) interviewCount,if(t6.deptCount>0,'有','无') orgTree,t7.contractId from (select a0.*,a1.project_id,a1.id spcId from company a0,search_project_company a1 where a0.id=a1.company_id) a ");
		sql.append(" left join (select company_id,count(distinct cv_id) cvCount from search_project_cv group by company_id) t1 on t1.company_id=a.id ");
		sql.append(" left join (select  aa.company_id,count(distinct aa.cv_id) cvlvCount from search_project_cv aa where exists( select 1 from cv a where ( exists (select 1 from cv_education a1 where a.id=a1.cv_id) or  exists (select 1 from cv_certificate a2 where a.id=a2.cv_id) or  exists (select 1 from cv_trainning a3 where a.id=a3.cv_id) or  exists (select 1 from cv_work a4 where a.id=a4.cv_id) ) and aa.cv_id=a.id) group by aa.company_id) t2 on t2.company_id=a.id ");
		sql.append(" left join (select t2.company_id,count(distinct t1.cv_id) cvContactCount from (select bb.ref_cvid cv_id,bb.project_id from search_project_cc_list bb where bb.project_id=? union all select aa.cv_id,aa.project_id from search_project_interview aa where aa.project_id=?) t1,search_project_cv t2 where t2.project_id=? and t1.project_id=t2.project_id and t1.cv_id=t2.cv_id  group by t2.company_id) t3 on t3.company_id=a.id ");
		sql.append(" left join (select company_id,count(distinct b.cv_id) cvRecomCount from contract_job_cv_recom a,company_cv b where a.cv_id=b.cv_id group by b.company_id) t4 on t4.company_id=a.id ");
		sql.append(" left join (select company_id,count(distinct c0.cv_id) interviewCount from search_project_interview c0,search_project_cv c1 where  c0.project_id=? and c0.cv_id=c1.cv_id group by company_id) t5 on t5.company_id=a.id ");
		sql.append(" left join ( select c1.company_id,count(*) deptCount from company c0,company_dept c1 where c0.id=c1.company_id group by c1.company_id ) t6 on t6.company_id=a.id ");
		sql.append(" left join (select a.company_id,max(b.id) contractId from customer a,customer_contract b,company c where a.id=b.cust_id and c.id=a.company_id group by a.company_id) t7 on t7.company_id=a.id ");
		sql.append(" ) pc ");
		sql.append(" where pc.projectId=? ");
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		*/
		sql.append(" select * from (  ");
		sql.append("   select a0.id,a.id companyId,a0.project_id projectId,a.name,a.full_name fullName,a.abbr_name abbrName, a.industry,a.property,a.type,a.tel,a.website,a.location,a.address,a.postcode  ");
		sql.append("   ,t7.contractId,ifnull(t2.cvCount,0) cvCount,ifnull(t2.lzCount,0) lzCount,ifnull(t2.cvlvCount,0) cvlvCount,ifnull(t3.cvContactCount,0) cvContactCount,ifnull(t4.cvRecomCount,0) cvRecomCount,ifnull(t5.interviewCount,0) interviewCount,if(t6.deptCount>0,'有','无') orgTree ");
		sql.append("   from search_project_company a0  inner join company a on a0.company_id=a.id ");
		//sql.append("   left join (select a.company_id,max(b.id) contractId from customer a,customer_contract b,company c,search_project_company d where d.project_id=? and c.id=d.company_id and a.id=b.cust_id and c.id=a.company_id group by a.company_id) t7 on t7.company_id=a.id  ");
		//sql.append(" left join (select a.company_id,c.id custId,max(b.id) contractId from customer a inner join search_project_company a0 on a0.company_id=a.id left join customer_contract b on a.id=b.cust_id left join customer_bd c on a.id=c.cust_id where a0.project_id=? and ifnull(b.end_d,now())>=now()  group by a.company_id,a.id) t7 on t7.company_id=a.id ");
		sql.append(" left join (select a.company_id,max(d.id) custId,max(ifnull(b.id,ifnull(c.contract_id,null))) contractId from customer a inner join search_project_company a0 on a0.company_id=a.company_id left join customer_bd d on a.id=d.cust_id left join customer_contract b on a.id=b.cust_id left join customer_contract_service_company c on a.id=c.cust_id where a0.project_id=? and b.end_d>=now() group by a.company_id) t7 on t7.company_id=a.id  ");
		sql.append("   left join (select a.company_id,count(distinct b.id) cvCount,sum(if(a.company_id<>b.company_id,1,0)) lzCount,sum(if(b.cv_status='F',1,0)) cvlvCount from company_cv a,cv b,search_project_cv c where c.project_id=?  and c.cv_id=a.cv_id and a.cv_id=b.id group by a.company_id) t2 on a.id=t2.company_id ");
		sql.append("   left join ( ");
		sql.append("     select aa.company_id,count(distinct aa.cv_id) cvContactCount from ( ");
		sql.append("       select a.cv_id,b.company_id,b.project_id from search_project_interview a,search_project_company b,company_cv c where b.project_id=? and a.cv_id=c.cv_id and a.project_id=b.project_id and b.company_id=c.company_id  ");
		sql.append("       union all ");
		sql.append("       select  a.ref_cvid cv_id,b.company_id,b.project_id from search_project_cc_list a,search_project_company b,company_cv c where b.project_id=? and a.project_id=b.project_id and a.ref_cvid=c.cv_id and b.company_id=c.company_id  ");
		sql.append("       union all ");
		sql.append("       select  a.cv_id,b.company_id,b.project_id from search_project_cv_crm a,search_project_company b,company_cv c,search_project_cv d where b.project_id=? and a.project_id=b.project_id and a.project_id=d.project_id and a.cv_id=d.cv_id and a.cv_id=c.cv_id and b.company_id=c.company_id  ");		
		sql.append("     ) aa group by aa.company_id ) t3 on t3.company_id=a.id     ");
		sql.append("    left join (select b.company_id,count(distinct a.cv_id) cvRecomCount from contract_job_cv_recom a,search_project_company b,company_cv c,contract_job_search_project d where b.project_id=? and a.cv_id=c.cv_id and b.company_id=c.company_id and a.job_id=d.job_id and d.project_id=b.project_id group by  b.company_id) t4 on t4.company_id=a.id    ");
		sql.append("    left join (select b.company_id,count(distinct a.cv_id) interviewCount from search_project_interview a,search_project_company b,company_cv c where b.project_id=? and a.cv_id=c.cv_id and a.project_id=b.project_id and b.company_id=c.company_id group by b.company_id)  t5 on t5.company_id=a.id    ");
		sql.append("    left join ( select c1.company_id,count(*) deptCount from search_project_company c0,company_dept c1 where c0.project_id=? and c0.company_id=c1.company_id and c1.name <> '缺省部门' group by c1.company_id ) t6 on t6.company_id=a.id  ");
		//sql.append("		 group by a.id ");
		sql.append("		 ) pc  ");
		sql.append("		 where pc.projectId=? ");
		
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		values.add(query.getProjectId());
		
		if(isNotEmpty(query.getCompanyName())) { 		    
		    sql.append(" and ( pc.name like ?  or  pc.fullName like ?)");
			values.add("%"+query.getCompanyName()+"%");
			values.add("%"+query.getCompanyName()+"%");
		}
		if(isNotEmpty(query.getSortColumns())){
			sql.append(" order by pc.").append(query.getSortColumns());
		}
		//a2
		sql.append(" order by pc.id desc ");
		return super.getJdbcDao().createPage(sql.toString(),values.toArray(), new MapObjectRowMapper(), query.getPageSize(), query.getPageNumber());
	}
	public Page findPage(SearchProjectCompanyQuery query) {
        //XsqlBuilder syntax,please see http://code.google.com/p/rapid-xsqlbuilder
        // [column]为字符串拼接, {column}为使用占位符. [column]为使用字符串拼接,如username='[username]',偷懒时可以使用字符串拼接 
        // [column] 为PageRequest的属性
        
		
		    Class[] os={SearchProjectCompanyQuery.class};
    		Class property= org.springframework.beans.BeanUtils.findPropertyType(query.getField_type(), os);
        if (property!=null){
        	 if ("java.lang.String".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(),query.getQuery());
	    			
	    		}
	    		else if ("java.lang.Integer".equals(property.getName())){
	    			
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseInteger(query.getQuery(), 0));
	    			
	    		}
	    		else if ("java.lang.Double".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), StringHelper.parseDouble(query.getQuery(), 0));
	    		}
	    		else if ("java.util.Date".equals(property.getName())){
	    			com.hh.bss.util.BeanUtils.setProperty(query, query.getField_type(), DateHelper.toDate(query.getQuery(), DateHelper.DEFAULT_DATE_FORMAT));
	    		}
        }	 
        //生成sql2的原因是为了不喜欢使用xsqlbuilder的同学，请修改生成器模板，删除本段的生成
        StringBuilder sql2 = new StringBuilder("select t from SearchProjectCompany t where 1=1 ");
        //StringBuilder sql2 = new StringBuilder("select t from SearchProjectCompany t where 1=1 ");
          if(isNotEmpty(query.getId())) {
            sql2.append(" and  t.id = :id ");
        }
          if(isNotEmpty(query.getProjectId())) {
            sql2.append(" and  t.projectId = :projectId ");
        }
          if(isNotEmpty(query.getCompanyId())) {
            sql2.append(" and  t.companyId = :companyId ");
        }
          if(isNotEmpty(query.getCompanyName())) {
              sql2.append(" and  t.companySimple.name like :companyName ");
              query.setCompanyName("%"+query.getCompanyName()+"%"); 
          }  
        if(isNotEmpty(query.getCreator())) {
            sql2.append(" and  t.creator like :creator ");
            query.setCreator("%"+query.getCreator()+"%"); 

        }
        if(isNotEmpty(query.getCreateDtBegin())) {
            sql2.append(" and  t.createDt >= :createDtBegin ");
        }
        if(isNotEmpty(query.getCreateDtEnd())) {
            sql2.append(" and  t.createDt <= :createDtEnd ");
        }
        if(isNotEmpty(query.getSortColumns())) {
            sql2.append(" order by t.:sortColumns ");
        }	
        
		return pageQuery(sql2.toString(),query);
	}
	

}
